/*
 * The code to create de DataBase:
 *
 * CREATE TABLE Show(ID integer primary key autoincrement,Name varchar(100), Code varchar(100));
 * CREATE TABLE Episode(ID integer primary key autoincrement, ShowID integer, Name varchar(100), Number varchar(10), Torrent varchar(100), Downloaded integer, Aired integer);
 * CREATE TABLE Subscription(ID integer primary key autoincrement, ShowID integer);
 *
 */
package tardistv.controller;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.logging.Level;
import java.util.logging.Logger;
import tardistv.model.Episode;
import tardistv.model.Show;

/**
 *
 * @author Carballude
 */
class DataBase {

    private static final String connectionString = "jdbc:sqlite:C:/Downloads/tardis.db";

    ArrayList<Show> getSubscribedShows() {
        ArrayList<Show> shows = new ArrayList<Show>();
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(connectionString);
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select Show.Name, Show.Code, Show.ID from Show, Subscription WHERE Show.ID=Subscription.ShowID;");
            while (rs.next()) {
                Show show = new Show();
                show.setName(rs.getString("Name"));
                show.setCode(rs.getString("Code"));
                show.setId(rs.getInt("ID"));
                shows.add(show);
            }
            stat.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return shows;
    }

    Show getEpisodes(Show show) {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(connectionString);
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("select * from Episode WHERE ShowID=" + show.getId() + ";");
            while (rs.next()) {
                Episode episode = new Episode();
                episode.setAired(rs.getBoolean("Aired"));
                episode.setName(rs.getString("Name"));
                episode.setNumber(rs.getString("Number"));
                episode.setTorrent(rs.getString("Torrent"));
                episode.setDownloaded(rs.getBoolean("Downloaded"));
                show.addEpisode(episode);
            }
            stat.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return show;
    }

    ArrayList<Show> getAvailableShows() {
        ArrayList<Show> shows = new ArrayList<Show>();
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(connectionString);
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT * FROM Show;");
            while (rs.next()) {
                Show show = new Show();
                show.setCode(rs.getString("Code"));
                show.setName(rs.getString("Name"));
                show.setId(rs.getInt("ID"));
                shows.add(show);
            }
            stat.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return shows;
    }

    private boolean episodeExists(Episode episode, Show show) {
        boolean ret = false;
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(connectionString);
            Statement stat = conn.createStatement();
            ResultSet rs = stat.executeQuery("SELECT COUNT(*) FROM Episode WHERE ShowID=" + show.getId() + " AND Number='" + episode.getNumber() + "';");
            while (rs.next()) {
                int cosa = rs.getInt(1);
                ret = (cosa > 0);
            }
            stat.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
            return false;
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
        return ret;
    }

    void saveSubscribedShow(Show show) {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(connectionString);
            Statement stat = conn.createStatement();
            stat.execute("INSERT INTO Subscription (ShowID) VALUES(" + show.getId() + ")");
            stat.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    void saveAvailableShow(Show show) {
        show.setName(show.getName().replace('\'', ' '));
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(connectionString);
            Statement stat = conn.createStatement();
            stat.execute("INSERT INTO Show (Name, Code) VALUES('" + show.getName() + "','" + show.getCode() + "')");
            stat.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    void saveEpisodes(Show show) {
        for (Episode episode : show.getEpisodes()) {
            if (!episodeExists(episode, show)) {
                saveEpisode(episode, show.getId());
            }
        }
    }

    void saveEpisode(Episode episode, int showId) {
        episode.setName(episode.getName().replace('\'', ' '));
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(connectionString);
            Statement stat = conn.createStatement();
            stat.execute("INSERT INTO Episode (ShowID, Name, Number, Torrent, Downloaded, Aired) VALUES(" + showId + ",'" + episode.getName() + "','" + episode.getNumber() + "','" + episode.getTorrent() + "'," + (episode.isDownloaded() ? 1 : 0) + "," + (episode.isAired() ? 1 : 0) + ")");
            stat.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    void deleteShowEpisodes(Show show) {
        try {
            Class.forName("org.sqlite.JDBC");
            Connection conn = DriverManager.getConnection(connectionString);
            Statement stat = conn.createStatement();
            stat.execute("DELETE Episode WHERE ShowID=" + show.getId());
            stat.close();
            conn.close();
        } catch (SQLException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(DataBase.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    void saveAvailableShows(ArrayList<Show> shows) {
        for (Show show : shows) {
            saveAvailableShow(show);
        }
    }

    void saveSubscribedShows(ArrayList<Show> shows) {
        for (Show show : shows) {
            saveSubscribedShow(show);
        }
    }
}
